package bank.savingspassbook.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import bank.datasource.DatabaseUtil;
import bank.passbook.vo.Passbook;
import bank.savingspassbook.vo.SavingsPassbook;

public class SavingsPassbookDAO {
	private DatabaseUtil dbUtil = DatabaseUtil.getInstance();
	private static SavingsPassbookDAO dao = new SavingsPassbookDAO();
	private SavingsPassbookDAO() {		}
	public static SavingsPassbookDAO getInstance(){
		return dao;
	}
	private String query = "select account_number, owner, money, createDate, interest, expiryDate from SavingsPassbook";
	
	public int	updateSavingsPassbookById(String account, int money) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update SavingsPassbook set money = money + ? where account_number = ?";
		int count = 0;
		try {
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, money);
			pstmt.setString(2, account);
			count = pstmt.executeUpdate();
		} finally {
			dbUtil.close(conn, pstmt);
		}
		return count;
	}
	
	public SavingsPassbook selectSavingsPassbookByAccountNo(String accountNumber) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = query + " where account_number = ?";
		ResultSet rs = null;
		SavingsPassbook savingsPassbook = null;
		
		try {
			conn = dbUtil.getConnection();;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, accountNumber);	
			rs = pstmt.executeQuery();
			if(rs.next()){
				savingsPassbook = new SavingsPassbook(rs.getString(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getDouble(5), rs.getString(6));
			}
		} finally {
			dbUtil.close(conn, pstmt, rs);
		}
		return savingsPassbook;
	}
	
	public ArrayList<SavingsPassbook> selectSavingsPassbookList() throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		ArrayList<SavingsPassbook> list = new ArrayList<SavingsPassbook>();
		
		try {
			conn = dbUtil.getConnection();;
			pstmt = conn.prepareStatement(query);
			rs = pstmt.executeQuery();
			while(rs.next()){
				list.add(new SavingsPassbook(rs.getString(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getDouble(5), rs.getString(6)));
			}
		} finally {
			dbUtil.close(conn, pstmt, rs);
		}
		return list;
	}
	
	public int updateSavingsPassbookByInterest() throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update savingspassbook set money = round(money * (interest + 1))";
		int count = 0;
		
		try {
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			count = pstmt.executeUpdate();
		} finally {
			dbUtil.close(conn, pstmt);
		}
		return count;
	}
}
